Plongée profonde dans les plans d’exécution

Frédéric Yhuel

Licence

Cette présentation est distribuée sous la licence CC BY-SA 4.0.

À propos

Frédéric Yhuel, consultant DBA depuis 4 ans chez Dalibo.

Support, audits et formations.

Plans d’exécution

  • décrit la façon d’accéder aux données, de les agréger, etc
  • EXPLAIN (ANALYZE, BUFFERS, SETTINGS) <query>
  • une structure en arbre
  • explain.dalibo.com

Scripts pour reproduire :

https://github.com/dalibo/misc/tree/main/fyhuel/pgsession16

Plongée de réadaptation

Nested Loop et arrondi

SELECT name, quantity FROM products p JOIN orders o
  ON (p.id = o.product_id)
  WHERE name LIKE 'ed%' AND short_name like 'ed%'
  ORDER BY quantity DESC LIMIT 10;


                            QUERY PLAN                                                    
------------------------------------------------------------------------
 Limit (actual time=13.623..13.624 rows=10 loops=1)
   ->  Sort (actual time=13.620..13.622 rows=10 loops=1)
         Sort Key: o.quantity DESC
         Sort Method: top-N heapsort  Memory: 26kB
         ->  Nested Loop (actual time=0.201..13.575 rows=117 loops=1)
               ->  Seq Scan on products p
                   (actual time=0.077..12.607 rows=381 loops=1)
                     Filter: ((name ~~ 'ed%'::text) AND
                              (short_name ~~ 'ed%'::text))
                     Rows Removed by Filter: 99619
               ->  Index Scan using orders_product_id_idx on orders o
                   (actual time=0.002..0.002 rows=0 loops=381)
                     Index Cond: (product_id = p.id)
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 8570b14f62..e8f603b145 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -1661,11 +1661,11 @@ ExplainNode(PlanState *planstate, List *ancestors,
        {
            if (es->timing)
                appendStringInfo(es->str,
-                       " (actual time=%.3f..%.3f rows=%.0f loops=%.0f)",
+                       " (actual time=%.3f..%.3f rows=%.1f loops=%.0f)",
                        startup_ms, total_ms, rows, nloops);
            else
                appendStringInfo(es->str,
-                       " (actual rows=%.0f loops=%.0f)",
+                       " (actual rows=%.1f loops=%.0f)",
                        rows, nloops);

                            QUERY PLAN                                                    
------------------------------------------------------------------------
 Limit (actual time=13.623..13.624 rows=10.0 loops=1)
   ->  Sort (actual time=13.620..13.622 rows=10.0 loops=1)
         Sort Key: o.quantity DESC
         Sort Method: top-N heapsort  Memory: 26kB
         ->  Nested Loop (actual time=0.201..13.575 rows=117.0 loops=1)
               ->  Seq Scan on products p
                   (actual time=0.077..12.607 rows=381.0 loops=1)
                     Filter: ((name ~~ 'ed%'::text) AND
                              (short_name ~~ 'ed%'::text))
                     Rows Removed by Filter: 99619
               ->  Index Scan using orders_product_id_idx on orders o
                   (actual time=0.002..0.002 rows=0.3 loops=381)
                     Index Cond: (product_id = p.id)

Et donc pas de vrai problème ici, juste un soucis d’affichage qui peut être déroutant, même pour un pratiquant aguerri des plans d’exécution.

Plongée sous-terraine

Jointure et parallélisation

CREATE TABLE products(
        id bigserial PRIMARY KEY,
        name TEXT,
        price INT
);

CREATE TABLE orders(
        id bigserial,
        product_id BIGINT REFERENCES products(id),
        quantity INT
);

CREATE INDEX ON orders (product_id);
CREATE INDEX ON products (price);
  • 400000 produits avec un prix qui varie entre 1 et 1000.
  • 3 millions d’achats répartis sur seulement 1000 produits
  • 3 millions d’achats répartis sur l’ensemble des produits.
INSERT INTO products (name, price)
  SELECT md5(i::TEXT), random() * 1000 FROM generate_series(1, 400000) i;

INSERT INTO orders (product_id, quantity)
  SELECT (i%1000) + 1, random()*10 FROM generate_series(1, 3000000) i;

INSERT INTO orders (product_id, quantity)
  SELECT (i%400000) + 1, random()*10 FROM generate_series(1, 3000000) i;

On a donc une répartition très hétérogène dans la table orders, avec la moitié des lignes qui référencent seulement 0.25%0.25\% de la table products. Malgré un ANALYZE effectué après les insertions, les statistiques seront faussées.

Pour une catégorie de prix donné, on veut le top 10 des produits qui se vendent le mieux.

SELECT
    name,
    sum(quantity)
FROM
    products p
    JOIN orders o ON (p.id = o.product_id)
WHERE
    price = 769
GROUP BY
    name
ORDER BY
    2 DESC
LIMIT 10;

1<=nb_workers<=31 <= nb\_workers <= 3

total_rowsnb_workers+1–0.3×nb_workers=rows\frac {total\_rows}{nb\_workers + 1 – 0.3 × nb\_workers} = rows

Autrement dit, PostgreSQL estime que le processus leader traite

  • 41%41\% des blocs avec un worker
  • 17%17\% des blocs avec deux workers
  • 3,2%3,2\% des blocs avec trois workers

et plus rien au-delà.

Parfois très différent de la réalité !

EXPLAIN (ANALYZE, VERBOSE) …

->  Parallel Seq Scan on public.products p
             (cost=0.00..5822.33 rows=162 width=41)
             (actual time=0.077..14.569 rows=130 loops=3)
      Output: p.id, p.name, p.price
      Filter: (p.price = 769)
      Rows Removed by Filter: 133204
      Worker 0:  actual time=0.005..11.914 rows=126 loops=1
      Worker 1:  actual time=0.019..11.559 rows=80 loops=1

nb_workers = 2

total_rows2.4=rows\frac {total\_rows}{2.4} = rows

->  Nested Loop  (cost=0.43..19818.47 rows=2431 width=37)
                 (actual time=0.071..55.054 rows=3969 loops=3)
     ->  Parallel Seq Scan on products p
               (cost=0.00..5822.33 rows=162 width=41)
               (actual time=0.025..12.600 rows=130 loops=3)
           Filter: (price = 769)
           Rows Removed by Filter: 133204
     ->  Index Scan using orders_product_id_idx on orders o
               (cost=0.43..76.13 rows=1026 width=12)
               (actual time=0.021..0.320 rows=31 loops=389)
           Index Cond: (product_id = p.id)

130×32.4=162.5\frac {130 × 3}{2.4} = 162.5

L’estimation pour le nombre de lignes retournées par la table externe est tout à fait correct !

Mais alors c’est quoi le problème ?? 🤔

parallel_seq_scan_cost=parallel\_seq\_scan\_cost =

seq_page_cost×Nblocs+seq\_page\_cost × N_{blocs} +

(cpu_tuple_cost+cpu_operator_cost)×Nrows2.4(cpu\_tuple\_cost + cpu\_operator\_cost) × \frac{N_{rows}}{2.4}

SELECT (current_setting('seq_page_cost')::numeric ×
          pg_relation_size('products') / 8192 +
                (
                    + current_setting('cpu_tuple_cost')::numeric
                    + current_setting('cpu_operator_cost')::numeric
                ) × 400000 / 2.4
        ) AS parallel_seq_scan_cost;
 parallel_seq_scan_cost 
------------------------
  5822.3333333333333333

Sans la division par 2.42.4, le coût serait 87398739 (au lieu de 58225822).

Mais cela reste très supérieur au coût d’un parcours d’index !!

(coût du parcours d’index : 452452)

Nested_loop_cost = Outer_table_scan_cost +

(index_scan_cost + cpu_tuple_cost × Ninner) × Nouter

Nested_loop_cost = Outer_table_scan_cost +

(index_scan_cost + cpu_tuple_cost × Ninner) × Nouter

= 5822 + (76.14 + 0.01 × 1026) × 162

Nested_loop_cost = Outer_table_scan_cost +

(index_scan_cost + cpu_tuple_cost × Ninner) × Nouter

= 5822 + (76.14 + 0.01 × 1026) × 162 = 19819

< 452 + (76.14 + 0.01 × 1026) × 389 = 34061

OK… mais pourquoi pas un parallel index scan ?? 🤔

min_parallel_index_scan_size

Spécifie la quantité minimale de donnée d’index qui doit être parcourue pour qu’un parcours parallèle soit envisagé. Notez qu’un parcours d’index parallèle ne touchera en général pas la totalité de l’index ;

il s’agit du nombre de pages [feuilles] que l’optimisateur pensera réellement toucher durant le parcours qui compte.

Quelle solution ?

Désactiver la parallélisation pour la table externe ?

Corriger les statistiques ?

Nested_loop_cost = Outer_table_scan_cost +

(index_scan_cost + cpu_tuple_cost × Ninner) × Nouter

= 5822 + (76.14 + 0.01 × 1026) × 162 = 19819

< 452 + (76.14 + 0.01 × 1026) × 389 = 34061

->  Nested Loop  (cost=0.43..19818.47 rows=2431 width=37)
                 (actual time=0.071..55.054 rows=3969 loops=3)
     ->  Parallel Seq Scan on products p
               (cost=0.00..5822.34 rows=162 width=41)
               (actual time=0.025..12.601 rows=130 loops=3)
           Filter: (price = 769)
           Rows Removed by Filter: 133204
     ->  Index Scan using orders_product_id_idx on orders o
               (cost=0.43..76.14 rows=1026 width=12)
               (actual time=0.021..0.321 rows=31 loops=389)
           Index Cond: (product_id = p.id)
SELECT tablename, attname, n_distinct FROM pg_stats
  WHERE tablename = 'orders' AND attname = 'product_id';

 tablename |  attname   | n_distinct 
-----------+------------+------------
 orders    | product_id |   5847

SELECT count(distinct(product_id)) FROM orders;
 count  
--------
 400000
ALTER TABLE orders ALTER COLUMN product_id SET (n_distinct = 400000);
ANALYZE orders;

Nested_loop_cost = Outer_table_scan_cost +

(index_scan_cost + cpu_tuple_cost × Ninner) × Nouter

= 5822 + (76.14 + 0.01 × 1026) × 162 = 19819

< 452 + (76.14 + 0.01 × 1026) × 389 = 34061

> 452 + (9.87 + 0.01 × 15) × 389 = 4349

Des questions ?

Dalibo recrute !! (celles et ceux qui ont tout suivi 😜)

Nos offres sur Welcome To The Jungle!